const db = require("../config/mysql");
const { v4: uuidv4 } = require("uuid");

class HouseModel {
  constructor() {
    this.db = db;

    // house 所有字段
    this.houseFields = [
      "house_id",
      "code",
      "name",
      "type",
      "condition",
      "user_id",
      "contact_phone",
      "address",
      "notes",
      "status",
      "created_at",
      "updated_at",
    ]
      .map((f) => `h.${f}`)
      .join(", ");

    // house_area 所有字段
    this.areaFields = [
      "area_id",
      "code",
      "house_id",
      "area_name",
      "storage_type",
      "`condition`",
      "status",
      "notes",
      "created_at",
      "updated_at",
    ]
      .map((f) => `ha.${f}`)
      .join(", ");
  }

  // 仓库 (House)
  // 检查仓库编号或名称是否已存在
  async isHouseCodeOrNameExist(type, value, excludeId = null) {
    let sql = `SELECT house_id FROM house WHERE ${type} = ? AND status != -1`;
    const params = [value];

    if (excludeId !== null) {
      sql += ` AND house_id != ?`;
      params.push(excludeId);
    }

    const [rows] = await this.db.query(sql, params);
    return rows.length > 0;
  }

  // 插入新仓库，生成 Code (WHxxx)
  async addHouseWithCodeTransaction(data) {
    let connection;
    try {
      // 从连接池获取连接并开始事务
      connection = await this.db.getConnection();
      await connection.beginTransaction();

      // 临时占位符，确保它在第一次 INSERT 时是唯一的非 NULL 值
      const tempCode = uuidv4();

      // 第一次插入 INSERT
      const insertSql = `
                INSERT INTO house 
                    (code, name, type, \`condition\`, user_id, contact_phone, address, notes, status) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;

      const insertParams = [
        tempCode, // 传入临时Code
        data.name,
        data.type,
        data.condition,
        data.user_id,
        data.contact_phone,
        data.address || null,
        data.notes || null,
        data.status || 1, // status 默认 1
      ];

      const [insertResult] = await connection.query(insertSql, insertParams);
      const houseId = insertResult.insertId;

      // 生成最终 Code
      const CODE_PREFIX = "WH";
      // 编号是 WH + 3位ID
      const idString = String(houseId).padStart(3, "0");
      const finalCode = `${CODE_PREFIX}${idString}`;

      // 第二次更新 UPDATE (回写最终 Code)
      const updateSql = `UPDATE house SET code = ? WHERE house_id = ?`;
      await connection.query(updateSql, [finalCode, houseId]);

      // 提交事务
      await connection.commit();

      return { houseId, finalCode };
    } catch (error) {
      // 事务出错，执行回滚
      if (connection) {
        await connection.rollback();
      }

      console.error(
        "HouseModel.addHouseWithCodeTransaction error:",
        error.message
      );
      throw new Error("新增仓库失败");
    } finally {
      // 释放连接
      if (connection) {
        connection.release();
      }
    }
  }

  // 更新仓库信息
  async updateHouse(houseId, data) {
    const updateData = { ...data };
    delete updateData.code;
    delete updateData.house_id;
    if (Object.keys(updateData).length === 0) return 0;

    // 使用占位符 ? 进行批量更新
    const updateSql = `UPDATE house SET ? WHERE house_id = ? AND status != -1`;
    const [result] = await this.db.query(updateSql, [updateData, houseId]);
    return result.affectedRows;
  }

  // 获取仓库详情
  async getHouseDetail(houseId) {
    const sql = `
            SELECT 
                ${this.houseFields}, u.user_name as manager_name, u.phone as manager_phone
            FROM 
                house h
            LEFT JOIN 
                user u ON h.user_id = u.user_id
            WHERE 
                h.house_id = ? AND h.status != -1`;
    const [rows] = await this.db.query(sql, [houseId]);
    return rows.length > 0 ? rows[0] : null;
  }

  // 获取仓库列表（分页、筛选）
  async getHouseList({ page = 1, pageSize = 10, name, type, status, userId }) {
    let countSql = `SELECT COUNT(*) AS total FROM house h WHERE h.status != -1`;

    let listSql = `
            SELECT 
                ${this.houseFields},
                u.user_name as manager_name,
                COUNT(ha.area_id) as area_count
            FROM 
                house h
            LEFT JOIN 
                user u ON h.user_id = u.user_id
            LEFT JOIN
                house_area ha ON h.house_id = ha.house_id AND ha.status != -1
            WHERE
                h.status != -1`;

    const params = [];
    const countParams = [];

    // 筛选条件
    if (name) {
      const likeName = `%${name}%`;
      listSql += ` AND h.name LIKE ?`;
      countSql += ` AND h.name LIKE ?`;
      params.push(likeName);
      countParams.push(likeName);
    }

    if (type !== undefined && type !== null) {
      const typeInt = parseInt(type);
      listSql += ` AND h.type = ?`;
      countSql += ` AND h.type = ?`;
      params.push(typeInt);
      countParams.push(typeInt);
    }

    if (status !== undefined && status !== null) {
      const statusInt = parseInt(status);
      listSql += ` AND h.status = ?`;
      countSql += ` AND h.status = ?`;
      params.push(statusInt);
      countParams.push(statusInt);
    }
    if (userId) {
      const userIdInt = parseInt(userId);
      listSql += ` AND h.user_id = ?`;
      countSql += ` AND h.user_id = ?`;
      params.push(userIdInt);
      countParams.push(userIdInt);
    }

    // 分页
    const offset = (page - 1) * pageSize;
    listSql += ` 
        GROUP BY 
            h.house_id 
        ORDER BY 
            CASE WHEN h.status = 1 THEN 1 ELSE 0 END ASC,
            h.house_id ASC 
        LIMIT ?, ?`;
    params.push(offset, pageSize);

    // 执行查询
    const [countRows] = await this.db.query(countSql, countParams);
    const total = countRows[0].total;

    const [list] = await this.db.query(listSql, params);

    return { list, total };
  }

  // 软删除仓库 (status = 0)
  async deactivateHouse(houseId) {
    const sql = `UPDATE house SET status = 0, updated_at = NOW() WHERE house_id = ? AND status = 1`;
    const [result] = await this.db.query(sql, [houseId]);
    return result.affectedRows;
  }

  // 仓库区域 (HouseArea)
  // 检查区域 Code 或 (house_id, area_name) 组合是否已存在
  async isAreaCompositeKeyExist(code, houseId, areaName, excludeId = null) {
    let codeExist = false;
    let nameExist = false;

    // 检查 Code 唯一性
    if (code) {
      let codeSql = `SELECT area_id FROM house_area WHERE code = ? AND status != 1`;
      const codeParams = [code];
      if (excludeId) {
        codeSql += ` AND area_id != ?`;
        codeParams.push(excludeId);
      }
      const [codeRows] = await this.db.query(codeSql, codeParams);
      codeExist = codeRows.length > 0;
    }

    // 检查 (house_id, area_name) 组合唯一性
    if (houseId && areaName) {
      let nameSql = `SELECT area_id FROM house_area WHERE house_id = ? AND area_name = ?`;
      const nameParams = [houseId, areaName];
      if (excludeId) {
        nameSql += ` AND area_id != ?`;
        nameParams.push(excludeId);
      }
      const [nameRows] = await this.db.query(nameSql, nameParams);
      nameExist = nameRows.length > 0;
    }

    return { codeExist, nameExist };
  }

  // 插入新区域，生成 Code (WHxxx-XXyy)
  async addAreaWithCodeTransaction(data, houseCode, areaPrefix) {
    const connection = await this.db.getConnection();
    try {
      await connection.beginTransaction();

      // 生成一个临时 Code 占位符
      const tempCode = uuidv4();

      const insertSql = `INSERT INTO house_area (code, house_id, area_name, storage_type, \`condition\`, notes, status) VALUES (?, ?, ?, ?, ?, ?, ?)`;

      const insertValues = [
        tempCode, // 传递临时 Code
        data.house_id,
        data.area_name,
        data.storage_type,
        data.condition || null,
        data.notes || null,
        data.status !== undefined ? data.status : 1,
      ];

      const [insertResult] = await connection.query(insertSql, insertValues);
      const areaId = insertResult.insertId;

      // 生成 Code (WHxxx-XXyyy)
      const idString = String(areaId).padStart(3, "0");
      const finalCode = `${houseCode}-${areaPrefix}${idString}`;

      // 回写 Code
      const updateSql = `UPDATE house_area SET code = ? WHERE area_id = ?`;
      await connection.query(updateSql, [finalCode, areaId]);

      await connection.commit();
      return { areaId, finalCode };
    } catch (error) {
      await connection.rollback();
      console.error("错误：", error.message);
      throw new Error(`新增仓库区域失败：${error.message}`);
    } finally {
      connection.release();
    }
  }

  // 获取仓库区域列表
  async getAreasByHouseId(houseId, { areaName, status }) {
    const params = [houseId];
    const totalParams = [houseId];

    const countSql = `
        SELECT COUNT(ha.area_id) AS total
        FROM house_area ha
        WHERE ha.house_id = ? AND ha.status != -1`;

    const [countRows] = await this.db.query(countSql, totalParams);
    const total = countRows[0].total || 0;

    let listSql = `
        SELECT 
            ${this.areaFields}, h.name as house_name, h.code as house_code
        FROM 
            house_area ha
        JOIN 
            house h ON ha.house_id = h.house_id
        WHERE 
            ha.house_id = ?`;

    if (status !== undefined && status !== null && status !== "") {
      const statusInt = parseInt(status);
      listSql += ` AND ha.status = ?`;
      params.push(statusInt);
    } else {
      listSql += ` AND ha.status != -1`;
    }
    if (areaName) {
      listSql += ` AND ha.area_name LIKE ?`;
      params.push(`%${areaName}%`);
    }
    // 排序
    listSql += ` ORDER BY ha.status DESC, ha.code ASC`;
    const [list] = await this.db.query(listSql, params);
    return { list, total };
  }

  // 获取区域详情
  async getAreaDetail(areaId) {
    const sql = `
            SELECT 
                ${this.areaFields}, h.name as house_name, h.code as house_code
            FROM 
                house_area ha
            JOIN 
                house h ON ha.house_id = h.house_id
            WHERE 
                ha.area_id = ? AND ha.status != -1`;
    const [rows] = await this.db.query(sql, [areaId]);
    return rows.length > 0 ? rows[0] : null;
  }

  // 更新区域信息
  async updateHouseArea(areaId, data) {
    const updateData = { ...data };
    delete updateData.code;
    delete updateData.area_id;
    delete updateData.house_id; // 不允许修改所属仓库

    if (Object.keys(updateData).length === 0) return 0;

    const updateSql = `UPDATE house_area SET ? WHERE area_id = ? AND status != -1`;
    const [result] = await this.db.query(updateSql, [updateData, areaId]);
    return result.affectedRows;
  }

  // 软删除区域 (status = 0)
  async deactivateHouseArea(areaId) {
    const sql = `UPDATE house_area SET status = 0, updated_at = NOW() WHERE area_id = ? AND status IN (1, 2)`;
    const [result] = await this.db.query(sql, [areaId]);
    return result.affectedRows;
  }
}

module.exports = HouseModel;
